﻿use master
go

create database Students
on
(
	name=Students,
	filename='D:\zuoye\Studens.mdf',
	size=10MB,
	maxsize=50MB,
	filegrowth=10%
)
log on
(
	name=Students_log,
	filename='D:\zuoye\Studens_log.ldf',
	size=10MB,
	maxsize=50MB,
	filegrowth=10%
)
go

use Students
go

create table ClassInfo
 (
	ClassID int primary Key identity(1,1),
	ClassName nvarchar(20) unique not null,
 )  
create table Student
(
	StuID int primary Key identity(1,1),
	ClassID int references ClassInfo(ClassID),
	StuName nvarchar(20) not null,
	StuSex nchar(1) default('') check(StuSex=''or StuSex='Ů'),--StuSex in '','Ů'
	StuBrithday date,
	StuPhone nvarchar(11) unique not null,
	StuAddress nvarchar(200)
)
create table Course
(
	CourseID int primary Key identity(1,1),
	CourseName nvarchar(50) unique not null,
	CourseCredit int default(1) not null check(CourseCredit>='1' or CourseCredit<='5')
)
create table Score
(
	ScoreID int primary Key identity(1,1),
	StuID int references Student(StuID),
	CourseID int references Course(CourseID),
	Score decimal(5,2) unique not null
)